<?php
/**
 * Object Relational Persistence Mapping Class for table: payment_collection
 *
 * @package	com.clearhealth
 * @author	ClearHealth Inc.
 */
class PaymentCollection extends ORDataObject {

	/**#@+
	 * Fields of table: payment_collection mapped to class members
	 */
	var $payment_collection_id		= '';
	var $cashier_id		= '';
	var $patient_id		= '';
	var $insurance 		= '';
	var $ticket_item_id		= '';
	var $date		= '';
	var $amount		= '';
	var $type		= '';
	var $notes		= '';
	/**#@-*/

	const PAYMENT_BEFORE = "before";
	const PAYMENT_WHOLE = "whole";
	
	/**
	 * DB Table
	 */
	var $_table = 'payment_collection';

	/**
	 * Primary Key
	 */
	var $_key = 'payment_collection_id';
	
	/**
	 * Internal Name
	 */
	var $_internalName = 'PaymentCollection';

	/**
	 * Handle instantiation
	 */
	function PaymentCollection() {
		parent::ORDataObject();
	}

	
	/**#@+
	 * Field: date, time formatting
	 */
	function get_date() {
		return $this->_getDate('date');
	}
	function set_date($date) {
		$this->_setDate('date',$date);
	}
	/**#@-*/
	/**
	 * @author hdviet
	 * modified to localize for others
	 */
	function getFullDate($id) {
		
		$sql = "select date from payment_collection where payment_collection_id = $id";
		$res = $this->_execute($sql);
		return $res->fields['date'];
	}
	
	function updateRealAmount($amount, $paymentColectionId, $note) {
		$sql = "update payment_collection set amount = $amount, notes = '$note' where payment_collection_id = $paymentColectionId";		
		$this->_execute($sql);
		
	}
	
	public function add($cashierId, $patientId, $ticketItemId, $amount, $type, $notes) {
		$ticketItem = Celini::newORDO('TicketItem', $ticketItemId);
		$ticket = Celini::newORDO('Ticket', $ticketItem->get('ticket_id'));
		$promotionProgram = $ticket->get('sale_program_store_id');
		
		$insurance = 'no';
		if (empty($promotionProgram)) {
			$ins = Celini::newORDO('Insurance');
			$res = $ins->isExistPatient($patientId);
			if ($res) {
				$insurance = 'yes';
			}
		}				
		
		$data = array(
			'patient_id'		=> $patientId,
			'insurance'			=> $insurance,
			'cashier_id'		=> $cashierId,
			'ticket_item_id'	=> $ticketItemId,
			'amount'			=> $amount,
			'type'				=> $type,
			'notes'				=> $notes);
		$this->populateArray($data);
		$this->persist();
		$paymentId = $this->get_id();
		$sql = "UPDATE payment_collection SET date = NOW() WHERE payment_collection_id = $paymentId";
		$this->_execute($sql);
	}
	
	public function isCollected($ticketItemId) {
		$res = $this->_execute("select payment_collection_id from $this->_table where ticket_item_id = $ticketItemId");
		if ($res && !$res->EOF) {
			return true;
		}
		return false;
	}
	
	public function getRealPaymentOfTicketItem($ticketItemId) {
		$ds = new Datasource_sql();
		$sql = array(
			'cols'	=> "amount, type",
			'from'	=> "$this->_table",
			'where'	=> "ticket_item_id = $ticketItemId"			
		);
		$ds->setup($this->_db, $sql);
		return $ds->toArray();
	}
	
	public function getObjectByTicketId($ticketId) {
		$sql = "SELECT $this->_key 
			FROM $this->_table p
				INNER JOIN ticket_item ti ON p.ticket_item_id = ti.ticket_item_id 
			WHERE ti.ticket_id = $ticketId
			ORDER BY p.date DESC
			LIMIT 1";
		$res = $this->_execute($sql);
		if ($res && !$res->EOF) {
			return Celini::newORDO('PaymentCollection', $res->fields[$this->_key]);	
		}
		return false;
	}
	
	public function statisticalPaymentByDepartmentId($departmentId, $fromDate = '', $toDate = '', 
			$onlyInsurancePatient = false, $cashierId = false) {
		$ds = new Datasource_sql();		
		$sql = array(
			'cols'	=> "p.patient_id, p.cashier_id, p.ticket_item_id, p.notes, 
						pe.entry_time entry_time,
						p.date collection_time,						 
						ti.patient_price patient_payment, p.amount real_payment",
			'from'	=> "payment_collection p			  	
			  	INNER JOIN ticket_item ti ON p.ticket_item_id = ti.ticket_item_id
			  	INNER JOIN ticket t ON ti.ticket_id = t.ticket_id
			  	INNER JOIN patient_entry pe ON t.ticket_id = pe.ticket_id
			  	",
			'where'	=> " t.department_id = $departmentId", 					
			'orderby'	=> "p.date"
		);
		
		if (!empty($fromDate)) {					
			$sql['where'] .= " AND DATE(p.date) >= '$fromDate'";
		}
		if (!empty($toDate)) {			
			$sql['where'] .= " AND DATE(p.date) <= '$toDate'";
		}
		if (empty($fromDate) && empty($toDate)) {
			$sql['where'] .= " AND DATE(p.date) = CURRENT_DATE()";
		}
		if ($onlyInsurancePatient) {
			$sql['where'] .= " AND p.insurance = 'yes'";			
		}
		if ($cashierId) {
			$sql['where'] .= " AND p.cashier_id = $cashierId";
		}
		
		$ds->setup($this->_db, $sql);
		return $ds->toArray();
	}		
	
	public function statisticalPayment($fromDate = '', $toDate = '', 
			$onlyInsurancePatient = false, $cashierId = false) {
		$ds = new Datasource_sql();
		$sql = array(
			'cols'	=> "p.patient_id, p.cashier_id, p.ticket_item_id, p.notes, 
						pe.entry_time entry_time,
						p.date collection_time,
						t.name ticket_name, t.patient_payment_without_discount total_payment, t.insurance_payment, 
						t.discount_payment, t.patient_payment, t.real_payment",
			'from'	=> "payment_collection p			  	
			  	INNER JOIN ticket_item ti ON p.ticket_item_id = ti.ticket_item_id
			  	INNER JOIN ticket t ON ti.ticket_id = t.ticket_id
			  	INNER JOIN patient_entry pe ON t.ticket_id = pe.ticket_id",
			'where'	=> " p.type ='" . self::PAYMENT_WHOLE . "'", 					
			'orderby'	=> "p.date"
		);
						
		if (!empty($fromDate)) {					
			$sql['where'] .= " AND DATE(p.date) >= '$fromDate'";
		}
		if (!empty($toDate)) {			
			$sql['where'] .= " AND DATE(p.date) <= '$toDate'";
		}
		if (empty($fromDate) && empty($toDate)) {
			$sql['where'] .= " AND DATE(p.date) = CURRENT_DATE()";
		}
		if ($onlyInsurancePatient) {
			$sql['where'] .= " AND p.insurance = 'yes'";			
		}
		if ($cashierId) {
			$sql['where'] .= " AND p.cashier_id = $cashierId";
		}
		
		$ds->setup($this->_db, $sql);
		return $ds->toArray();
	}
	
	public function getDuplicatedRecords($patientId, $date) {
		
		$sql = "SELECT c.patient_id, c.cashier_id, c.payment_collection_id, c.amount, c.date
				FROM payment_collection c												
				WHERE DATE(c.date) = '". $date ."' AND c.type = '" . self::PAYMENT_WHOLE . "'  AND c.patient_id = $patientId				
				ORDER BY c.date
				";
		$res = $this->_execute($sql);
			
		$result = array();
		$count = 0;		
		$p = Celini::newORDO('Person');
		if ($res && !$res->EOF) {
			while (!$res->EOF) {		
				$p->setup($res->fields['cashier_id']);		
				$temp = array();
				$temp['count'] = ++$count;				
				$temp['cashier'] = $p->get('name');						
				$temp['payment_collection_id'] = $res->fields['payment_collection_id'];							
				$temp['amount'] = L10nHelper::renderNumber($res->fields['amount']); 
				$temp['date'] = L10nHelper::renderDateTime($res->fields['date'],'short', 'datetime');				
				$result[] = $temp;								
				$res->moveNext();					
			}
			return $result;
			
		}
		return false;
	}
	
	public function getPaymentRecords($date) {
		
			$sql = "SELECT count(c.patient_id) no, c.patient_id, c.payment_collection_id, SUM(c.amount) total_amount, c.date
				FROM payment_collection c												
				WHERE DATE(c.date) = '". $date ."' AND c.type = '" . self::PAYMENT_WHOLE . "' 
				GROUP BY c.patient_id
				ORDER BY c.date
				";
			$res = $this->_execute($sql);
			
			$result = array();
			$count = 0;
			$per = Celini::newORDO('Person');
			$patient = Celini::newORDO('Patient');
			if ($res && !$res->EOF) {
				while (!$res->EOF) {
					$per->setup($res->fields['patient_id']);
					$patient->setup($res->fields['patient_id']);
					$temp = array();
					$temp['count'] = ++$count;	
					$temp['patient_id'] = $res->fields['patient_id'];
					$temp['address'] = $per->getAddress();		
					$temp['payment_collection_id'] = $res->fields['payment_collection_id'];
					$temp['patient_code'] = $patient->get('patient_code');
					$temp['patient_name'] = $per->get('name');
				
					$temp['no'] = $res->fields['no'];
					$temp['total_amount'] = L10nHelper::renderNumber($res->fields['total_amount']); 
					$temp['date'] = L10nHelper::renderDateTime($res->fields['date'],'short', 'datetime');					
					$temp['selected_date'] = $date;
					$result[] = $temp;								
					$res->moveNext();					
				}
				return $result;
				
			}
			return false;
	}
	
	public function getRecentPatient($userId, $limit = 20) {
		$date = date('Y-m-d');
		$sql = "SELECT pc.patient_id, pc.ticket_item_id, p.name, pa.patient_code
				FROM $this->_table pc
					INNER JOIN person p ON (pc.patient_id = p.person_id)
					INNER JOIN patient pa ON (pc.patient_id = pa.person_id)
				WHERE pc.cashier_id = $userId AND DATE(pc.date) = '$date'
				ORDER BY pc.date DESC
				LIMIT $limit";
		return $this->_execute($sql);
	}
}
?>
